package com.apes.fico.finance.respository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Objects;

/**
 * Created by lishikun on 2021/1/29 16:23
 */
@Component
public class WaterElectricityRentDao extends ApesDao {

    /**
     * 批量查询交易流水号
     * @param param
     * @return
     */
    public JSONArray queryAllTransactionFlow(JSONObject param,String company) {
        JSONObject request = new JSONObject();
        String sql = "SELECT a.id,a.dept_id, a.dept_company_id,to_char(a.year_month, 'yyyy-mm') year_month, a.type,\n" +
                "       A.water_amt, A.electricity_amt, A.rent_amt, A.other_amt, a.summary,a.company_id,a.valid\n" +
                "  FROM gl_Water_Electricity_Rent a\n" +
                " where 1 = 1\n" ;
        if (Objects.nonNull(param.get("yearMonth"))) {
            String yearMonth = param.getString("yearMonth");
            sql += " AND to_char(a.year_month,'yyyy-mm') = substr('"+ yearMonth +"',0,7)";
        }

        if (Objects.nonNull(param.get("dept"))) {
            JSONArray dept = param.getJSONArray("dept");
            dept.forEach(dept1->{
                request.put("dept", ((JSONObject) dept1).get("id"));
            });
            sql += " and a.dept_id = :dept ";
        }

        if (Objects.nonNull(param.get("type"))) {
            request.put("type", param.getString("type"));
            sql += " and a.type = :type ";
        }

        if (!"A0000".equals(company)) {
            sql += " AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.dept_company_id = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))";
        }
        return toJSONArray(find(sql, request));
    }
}
